{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "68bd8b46",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    商店ID 门店所在城市  渠道 性别群体   年龄群体 产品类别  客户数量  销售金额  订单数量  购买的产品数量    成本    单价  \\\n",
      "0  831.0     杭州  线下    女  20-24   T恤   1.0  59.0   1.0      1.0  49.0  59.0   \n",
      "\n",
      "     利润       订单日期   星期  \n",
      "0  10.0 2023-01-02  星期一  \n",
      "2047\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2047 entries, 0 to 2046\n",
      "Data columns (total 15 columns):\n",
      " #   Column   Non-Null Count  Dtype         \n",
      "---  ------   --------------  -----         \n",
      " 0   商店ID     2040 non-null   float64       \n",
      " 1   门店所在城市   2040 non-null   object        \n",
      " 2   渠道       2040 non-null   object        \n",
      " 3   性别群体     2040 non-null   object        \n",
      " 4   年龄群体     2040 non-null   object        \n",
      " 5   产品类别     2040 non-null   object        \n",
      " 6   客户数量     2040 non-null   float64       \n",
      " 7   销售金额     2040 non-null   float64       \n",
      " 8   订单数量     2040 non-null   float64       \n",
      " 9   购买的产品数量  2040 non-null   float64       \n",
      " 10  成本       2040 non-null   float64       \n",
      " 11  单价       2040 non-null   float64       \n",
      " 12  利润       2040 non-null   float64       \n",
      " 13  订单日期     2040 non-null   datetime64[ns]\n",
      " 14  星期       2040 non-null   object        \n",
      "dtypes: datetime64[ns](1), float64(8), object(6)\n",
      "memory usage: 240.0+ KB\n",
      "None\n",
      "              商店ID         客户数量         销售金额         订单数量      购买的产品数量  \\\n",
      "count  2040.000000  2040.000000  2040.000000  2040.000000  2040.000000   \n",
      "mean    752.912745     1.648039   158.340701     1.671078     1.891176   \n",
      "min     658.000000     1.000000    10.000000     1.000000     1.000000   \n",
      "25%     737.000000     1.000000    59.000000     1.000000     1.000000   \n",
      "50%     758.000000     1.000000    99.000000     1.000000     1.000000   \n",
      "75%     796.000000     2.000000   192.000000     2.000000     2.000000   \n",
      "max     831.000000    38.000000  5122.000000    39.000000    44.000000   \n",
      "std      45.051703     1.885295   236.991903     1.955650     2.422293   \n",
      "\n",
      "                成本           单价           利润                           订单日期  \n",
      "count  2040.000000  2040.000000  2040.000000                           2040  \n",
      "mean     46.406863    83.966667    72.403446  2023-09-04 23:12:00.000000256  \n",
      "min       9.000000    10.000000  -360.000000            2023-01-02 00:00:00  \n",
      "25%      29.000000    54.000000    14.000000            2023-08-12 00:00:00  \n",
      "50%      49.000000    79.000000    40.000000            2023-08-25 00:00:00  \n",
      "75%      59.000000    99.000000    90.000000            2023-10-12 00:00:00  \n",
      "max      99.000000   299.000000  3293.000000            2023-11-11 00:00:00  \n",
      "std      20.842744    47.735846   150.656343                            NaN  \n",
      "Index(['商店ID', '门店所在城市', '渠道', '性别群体', '年龄群体', '产品类别', '客户数量', '销售金额', '订单数量',\n",
      "       '购买的产品数量', '成本', '单价', '利润', '订单日期', '星期'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import os,sys\n",
    "excel_file = \"../4-测试数据/优衣库销售数据.xlsx\"\n",
    "df = pd.read_excel(excel_file)\n",
    "print(df.head(1))\n",
    "print(df.shape[0])\n",
    "print(df.info())\n",
    "print(df.describe())\n",
    "print(df.columns)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fca2e07e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(574, 15)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 行筛选数据选择\n",
    "# 性别女性，销售金额大于100\n",
    "df_female_consume = df[(df['性别群体'] == '女') & (df['销售金额'] > 100)]\n",
    "df_female_consume.shape\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8e1142c8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "高价值客户群体TOP10:\n",
      "   门店所在城市 性别群体   年龄群体      销售金额        利润   客户数量   订单数量\n",
      "22     深圳    女  30-34  36835.97  17051.97  413.0  419.0\n",
      "21     深圳    女  25-29  27581.88  12431.88  269.0  276.0\n",
      "23     深圳    女  35-39  24577.49  11573.49  252.0  253.0\n",
      "20     深圳    女  20-24  12881.27   6393.27  122.0  124.0\n",
      "32     深圳    男  30-34  12479.47   5762.47  127.0  131.0\n",
      "60     重庆    女  30-34  12000.93   5597.93  130.0  134.0\n",
      "3      杭州    女  35-39   9854.27   4690.27   86.0   90.0\n",
      "24     深圳    女  40-44   9785.00   4358.00  104.0  104.0\n",
      "58     重庆    女  20-24   9383.66   3662.66  104.0  105.0\n",
      "31     深圳    男  25-29   8828.57   2759.57  111.0  114.0\n"
     ]
    }
   ],
   "source": [
    "high_value_segments = df.groupby(['门店所在城市', '性别群体', '年龄群体']).agg({\n",
    "    '销售金额': 'sum',\n",
    "    '利润': 'sum', \n",
    "    '客户数量': 'sum',\n",
    "    '订单数量': 'sum'\n",
    "}).reset_index()\n",
    "\n",
    "# 筛选销售金额前10的组合\n",
    "top_segments = high_value_segments.nlargest(10, '销售金额')\n",
    "print(\"高价值客户群体TOP10:\")\n",
    "print(top_segments)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
